#!/bin/bash
# File Name: get_info_for_md.sh
# Author: moshan
# mail: mo_shan@yeah.net
# Created Time: 2019-03-22 11:41:43
# Function: 统计线上无主键的表
#########################################################################
db_name="db_cmdb"
mysql_user="moshan"
mysql_passwd="xxxxxxxxxx"
ip_port_list="ip_port.list"
host_list="192.168.2.142"
mysql_port=3306
cat <<EOF >initial.sql
drop table if exists check_result;
CREATE TABLE if not exists check_result (
  id int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增列主键',
  product_name char(50) DEFAULT NULL COMMENT '业务表所在的产品线',
  project_name char(50) DEFAULT NULL COMMENT '业务表所在的项目',
  ha_group_name char(50) DEFAULT NULL COMMENT '业务表所在的高可用组名称',
  host char(15) DEFAULT NULL COMMENT '业务表所在的MySQL主机IP地址',
  port char(5) DEFAULT NULL COMMENT '业务表所在的主机MySQL实例端口号',
  db_name varchar(128) DEFAULT NULL COMMENT '业务表所在的MySQL database',
  table_name varchar(128) DEFAULT NULL COMMENT '业务表的表名',
  partition_info varchar(4000) DEFAULT NULL COMMENT '业务表的表分区信息',
  table_row bigint DEFAULT NULL COMMENT '业务表的数据行(估算值)',
  table_size varchar(50) DEFAULT NULL COMMENT '业务表的数据量大小(index+data估算值)',
  engin_name char(20) DEFAULT NULL COMMENT '业务表用的存储引擎',
  pk_stat char(4) DEFAULT NULL COMMENT '业务表的主键状态值YES|NO',
  fk_stat char(4) DEFAULT NULL COMMENT '业务表的外键状态值YES|NO',  
  fk_references varchar(128) DEFAULT NULL COMMENT '业务表的外键表的表名',
  uk_stat char(4) DEFAULT NULL COMMENT '业务表的唯一索引状态值YES|NO',
  auto_col_stat char(4) DEFAULT NULL COMMENT '业务表的自增列属性状态值YES|NO',
  auto_col_name varchar(128) DEFAULT NULL COMMENT '业务表的自增列字段名称',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
begin;
EOF
mysql_comm="mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} ${db_name}"
${mysql_comm} -NBe "select tsi.ip_app ,tdi.port from t_server_info tsi join t_db_instance tdi on tsi.server_id=tdi.server_id join t_db_ha_group_member tdhgm on tdhgm.member_id=tdi.instance_id join t_db_ha_group tdhg on tdhg.ha_group_id=tdhgm.ha_group_id where tdhgm.role_name='写节点' and ha_type in ('pxc','mgr');" 2>/dev/null|grep -v "^172"|awk '{print $1":"$2}' >${ip_port_list} 2>/dev/null
echo "系统 zabbix semi_sync_tem_zabbix_datacenter 192.168.2.229 3333" > ${ip_port_list}
echo "系统 zabbix semi_sync_tem_zabbix 192.168.2.229 3307" >> ${ip_port_list}
${mysql_comm} -NBe "set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';select distinct tpdi.product_name, tpji.project_name, tdhg.ha_group_name, tsi.ip_app,   tdi.port from   t_server_info tsi   join t_db_instance tdi on tsi.server_id = tdi.server_id   join t_db_ha_group_member tdhgm on tdhgm.member_id = tdi.instance_id   join t_db_ha_group tdhg on tdhg.ha_group_id = tdhgm.ha_group_id   join t_db_database tdd on tdd.instance_id = tdi.instance_id   join t_project_info tpji on tdd.project_id = tpji.project_id   join t_product_info tpdi on tpdi.product_id = tpji.product_id where   tdhgm.role_name = '写节点'   and ha_type in ('pxc','mgr') and tsi.ip_app like '192%' group by tsi.ip_app,tdi.port;" >>${ip_port_list} 2>/dev/null
for ((i=1;i<=$(wc -l < ${ip_port_list});i++)) #i  in $(awk '{print }' <<< "${ip_port_list}")
do
    product_name="$(awk '{print $1}' <<< "$(sed -n "${i}p" ${ip_port_list})")"
    project_name="$(awk '{print $2}' <<< "$(sed -n "${i}p" ${ip_port_list})")"
    ha_group_name="$(awk '{print $3}' <<< "$(sed -n "${i}p" ${ip_port_list})")"
    host_list="$(awk '{print $4}' <<< "$(sed -n "${i}p" ${ip_port_list})")"
    mysql_port="$(awk '{print $5}' <<< "$(sed -n "${i}p" ${ip_port_list})")"
    mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} -NBe "show databases;" 2>/dev/null|grep -viE "^performance_schema$|^mysql$|^information_schema$|^sys$" > db_name.list
    for db in $(cat db_name.list)
    do
        mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} ${db} -NBe "show tables;" 2>/dev/null > table.list
        res_dir="${host_list}_${mysql_port}/${db}"
        res_file="${res_dir}/${host_list}_${mysql_port}_${db}_res.log"
        mkdir -p ${res_dir}
        echo "产品线,业务线,高可用组,主机IP,端口号,db_name,table_name,分区表,表的预估数据行,表的预估大小,存储引擎,主键状态,外键状态,外键列,唯一索引状态,自增列属性,自增列列名" > ${res_file}
        for table in $(cat table.list)
        do
            engin=$(mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} ${db} -NBe "show create table \`${table}\`\\G" 2> /dev/null|grep -c "ENGINE=InnoDB")
            fk=$(mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} ${db} -NBe "select count(*) from information_schema.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = \"${table}\";" 2>/dev/null)
            pri=$(mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} ${db} -e "desc \`${table}\`" 2> /dev/null|grep -c "PRI")
            #pri=$(mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} ${db} -NBe "show create table \`${table}\`\\G" 2> /dev/null|grep -c "PRIMARY KEY")
            if [ "${pri}x" == "0x" ]
            then
                pri_stat="NO"
                mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} ${db} -NBe "show create table \`${table}\`\\G" 2> /dev/null > ${res_dir}/pri_${table}.log
            else
                pri_stat="YES"
            fi
            if [ "${fk}x" == "1x" ]
            then
                fk_stat="YES"
                fk_references=$(mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} ${db} -NBe "select concat(CONSTRAINT_SCHEMA,'.',TABLE_NAME) from information_schema.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = \"${table}\";" 2>/dev/null)
                mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} ${db} -NBe "show create table \`${table}\`\\G" 2> /dev/null > ${res_dir}/fk_${table}.log
            else
                fk_stat="NO"
                fk_references="NULL"
            fi
            if [ "${engin}x" == "0x" ]
            then
                mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} ${db} -NBe "show create table \`${table}\`\\G" 2> /dev/null > ${res_dir}/engin_${table}.log
                engin_name="$(grep "ENGINE=" ${res_dir}/engin_${table}.log|awk -F"ENGINE=" '{print $2}'|awk '{print $1}')"
            else
                engin_name="INNODB"
            fi
            if [ "${pri_stat}x" == "YESx" -a "${fk_stat}x" == "NOx" -a "${engin_name}x" == "INNODBx" ]
            then
                continue
            fi
            uk=$(mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} ${db} -NBe "show create table \`${table}\`\\G" 2> /dev/null|grep -c "UNIQUE KEY")
            if [ "${uk}x" != "0x" ]
            then
                uk_stat="YES"
            else
                uk_stat="NO"
            fi
            auto_col=$(mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} ${db} -NBe "show create table \`${table}\`\\G" 2> /dev/null|grep -c "AUTO_INCREMENT")
            if [ "${auto_col}x" != "0x" ]
            then
                auto_col_name=$(mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} ${db} -NBe "desc \`${table}\`" 2> /dev/null|grep -i "AUTO_INCREMENT"|awk '{print $1}')
                auto_col_stat="YES"
            else
                auto_col_name="NULL"
                auto_col_stat="NO"
            fi
            partition_name=($(mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} -NBe "SELECT partition_name FROM information_schema.PARTITIONS WHERE table_schema = '${db}' AND table_name = '${table}';" 2>/dev/null))
            #if [ "${partition_name}x" == "NULLx" ]
            #then
            #    mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} ${db} -NBe "show create table \`${table}\`\\G" 2> /dev/null > ${res_dir}/par_${table}.log
            #fi
            #table_row_size="$(mysql -u${mysql_user} -p${mysql_passwd} -h${host_list} -P${mysql_port} -NBe "select concat(TABLE_ROWS,':',(DATA_LENGTH+INDEX_LENGTH)/1024/1024) from information_schema.TABLES where TABLE_NAME='${table}' and TABLE_SCHEMA='${db}'" 2>/dev/null)"
            table_row="$(awk -F: '{print $1}' <<< "${table_row_size}")"
            table_size="$(awk -F: '{print $2}' <<< "${table_row_size}")"
            echo "${product_name},${project_name},${ha_group_name},${host_list},${mysql_port},${db},${table},${partition_name[@]},${table_row},${table_size}M,${engin_name},${pri_stat},${fk_stat},${fk_references},${uk_stat},${auto_col_stat},${auto_col_name}" >> ${res_file}
            #echo "insert into check_result(product_name,project_name,ha_group_name,host,port,db_name,table_name,partition_info,table_row,table_size,engin_name,pk_stat,fk_stat,fk_references,uk_stat,auto_col_stat,auto_col_name) select '${product_name}','${project_name}','${ha_group_name}','${host_list}','${mysql_port}','${db}','${table}','${partition_name[@]}','${table_row}','${table_size}M','${engin_name}','${pri_stat}','${fk_stat}','${fk_references}','${uk_stat}','${auto_col_stat}','${auto_col_name}';" >> initial.sql
        done
        [ "$(wc -l < ${res_file} 2>/dev/null)x" == "1x" ] && rm -f ${res_file}
        rmdir ${res_dir} 2>/dev/null
    done
    rmdir ${host_list}_${mysql_port} 2>/dev/null
done
echo "commit;" >> initial.sql
